using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.ScriptTask;

namespace SsisUtilities.PacMan.Components
{
    /// <summary>
    /// 
    /// </summary>
    public class PackageUtil
    {
        private const string DEFAULT_CONFIG_SUBSET_NAME = "*";

        private Package ssisPackage;
        private FileInfo packageFile;

        private string validationStatus = "";
        private string projectName = "Unknown";

        private List<string> infoMessages;
        private List<string> warningMessages;
        private List<string> errorMessages;

        #region Constructors

        /// <summary>
        /// Initializes a new instance of the <see cref="PackageUtil"/> class.
        /// </summary>
        /// <param name="package">The package.</param>
        public PackageUtil(FileInfo packageFile)
        {
            Application a = new Application();
            ssisPackage = a.LoadPackage(packageFile.FullName, null);
            this.packageFile = packageFile;
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="PackageUtil"/> class
        /// and sets its ProjectName property.
        /// </summary>
        /// <param name="packageFile">The package.</param>
        /// <param name="projectName">The name of the project that 
        /// contains the package</param>
        public PackageUtil(FileInfo packageFile, string projectName)
        {
            Application a = new Application();
            ssisPackage = a.LoadPackage(packageFile.FullName, null);
            this.packageFile = packageFile;
            this.projectName = projectName;
        }

        #endregion

        #region Public Properties

        /// <summary>
        /// Gets the ssis package.
        /// </summary>
        /// <value>The ssis package managed by the PackageUtil instance.</value>
        public Package SsisPackage
        {
            get
            {
                return ssisPackage;
            }
        }

        public string ValidationStatus
        {
            get
            {
                return validationStatus;
            }
        }

        public string ProjectName
        {
            get
            {
                return projectName;
            }

            set
            {
                projectName = value;
            }
        }


        public List<string> InfoMessages
        {
            get
            {
                return infoMessages;
            }
        }

        public List<string> WarningMessages
        {
            get
            {
                return warningMessages;
            }
        }

        public List<string> ErrorMessages
        {
            get
            {
                return errorMessages;
            }
        }


        public string PackageFileName
        {
            get
            {
                return packageFile.Name;
            }
        }

        public string PackageFilePath
        {
            get
            {
                return packageFile.FullName;
            }
        }


        #endregion

        /// <summary>
        /// Copy the code from a script task in the source paackage into the 
        /// current package.
        /// </summary>
        /// <param name="sourcePackage"></param>
        /// <remarks>
        /// Please note that this it not yet a generic method - it only works
        /// for the OnPreExecute event handler, as this is all I currently 
        /// need (remember - PacMan is primarily my personal dev utility :-)
        /// </remarks>
        public void CopyScriptTaskPrototype(PackageUtil sourcePackage)
        {
            // Basic logic taken from https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=941508&SiteID=1

            //const string CODE_MONIKER_TEMPLATE = "dts://Scripts/{0}/ScriptMain.vsaitem";
            //const string PROJ_MONIKER_TEMPLATE = "dts://Scripts/{0}/{0}.vsaproj";
            //const string BINN_MONIKER_TEMPLATE = "dts://Scripts/{0}/{0}.dll";

            //try
            //{
            //    // Get the source task
            //    TaskHost sourceScriptTaskHost = sourcePackage.ssisPackage.EventHandlers["OnPreExecute"].Executables[0] as TaskHost;
            //    ScriptTask sourceScriptTask = sourceScriptTaskHost.InnerObject as ScriptTask;

            //    // Get the target container
            //    if (!this.ssisPackage.EventHandlers.Contains("OnPreExecute"))
            //    {
            //        this.ssisPackage.EventHandlers.Add("OnPreExecute");
            //    }

            //    DtsEventHandler targetContainer = this.ssisPackage.EventHandlers["OnPreExecute"];

            //    // Delete the target task, if it exists
            //    if (targetContainer.Executables.Contains(sourceScriptTaskHost.Name))
            //    {
            //        targetContainer.Executables.Remove(sourceScriptTaskHost.Name);
            //    }

            //    TaskHost targetScriptTaskHost = targetContainer.Executables.Add("STOCK:SCRIPTTASK") as TaskHost;
            //    targetScriptTaskHost.Name = sourceScriptTaskHost.Name;
            //    targetScriptTaskHost.Description = sourceScriptTaskHost.Description;

            //    ScriptTask targetScriptTask = targetScriptTaskHost.InnerObject as ScriptTask;
            //    targetScriptTask.SetUniqueVsaProjectName();

            //    string sourceCodeMoniker = string.Format(CODE_MONIKER_TEMPLATE, sourceScriptTask.VsaProjectName);
            //    string targetCodeMoniker = string.Format(CODE_MONIKER_TEMPLATE, targetScriptTask.VsaProjectName);
                
            //    string sourceProjectMoniker = string.Format(PROJ_MONIKER_TEMPLATE, sourceScriptTask.VsaProjectName);
            //    string targetProjectMoniker = string.Format(PROJ_MONIKER_TEMPLATE, targetScriptTask.VsaProjectName);

            //    string sourceBinaryMoniker = string.Format(BINN_MONIKER_TEMPLATE, sourceScriptTask.VsaProjectName);
            //    string targetBinaryMoniker = string.Format(BINN_MONIKER_TEMPLATE, targetScriptTask.VsaProjectName);

            //    targetScriptTask.CodeProvider.PutSourceCode(targetCodeMoniker,
            //        sourceScriptTask.CodeProvider.GetSourceCode(sourceCodeMoniker));

            //    targetScriptTask.CodeProvider.PutSourceCode(targetProjectMoniker,
            //        sourceScriptTask.CodeProvider.GetSourceCode(sourceProjectMoniker));

            //    // We've commented this out due to errors at package runtime
            //    //targetScriptTask.CodeProvider.PutBinaryCode(targetBinaryMoniker,
            //    //    sourceScriptTask.CodeProvider.GetBinaryCode(sourceBinaryMoniker));

            //    //targetScriptTask.CodeProvider.RenameAll(sourceScriptTask.VsaProjectName,
            //    //    targetScriptTask.VsaProjectName);

            //    targetScriptTask.PreCompile = false; // We want to be able to say "true" here
            //    // but cannot until we figure out how to precompile the code

            //    targetScriptTaskHost.DelayValidation = sourceScriptTaskHost.DelayValidation;
            //    targetScriptTaskHost.Disable = sourceScriptTaskHost.Disable;
            //    targetScriptTaskHost.DisableEventHandlers = sourceScriptTaskHost.DisableEventHandlers;
            //    targetScriptTaskHost.FailPackageOnFailure = sourceScriptTaskHost.FailPackageOnFailure;
            //    targetScriptTaskHost.FailParentOnFailure = sourceScriptTaskHost.FailParentOnFailure;
            //    targetScriptTaskHost.ForceExecutionResult = sourceScriptTaskHost.ForceExecutionResult;
            //    targetScriptTaskHost.ForceExecutionValue = sourceScriptTaskHost.ForceExecutionValue;
            //    targetScriptTaskHost.IsolationLevel = sourceScriptTaskHost.IsolationLevel;
            //    targetScriptTaskHost.LocaleID = sourceScriptTaskHost.LocaleID;
            //    targetScriptTaskHost.LoggingMode = sourceScriptTaskHost.LoggingMode;
            //    targetScriptTaskHost.MaximumErrorCount = sourceScriptTaskHost.MaximumErrorCount;
            //    targetScriptTaskHost.SuspendRequired = sourceScriptTaskHost.SuspendRequired;
            //    targetScriptTaskHost.TransactionOption = sourceScriptTaskHost.TransactionOption;
            //}
            //catch (Exception ex)
            //{
            //    throw new ApplicationException("Could not copy script task - oh no!", ex);
            //}
        }

        public List<string> GetDiscreteExecutableTypes()
        {
            List<string> executables = new List<string>();

            foreach (Executable exe in ssisPackage.Executables)
            {
                string exeTypeName = string.Empty;

                if (exe is TaskHost)
                {
                    exeTypeName = (exe as TaskHost).InnerObject.ToString();
                }
                else if (exe is Sequence)
                {
                    exeTypeName = "Sequence Container";
                }
                else if (exe is ForEachLoop)
                {
                    exeTypeName = "Foreach Loop Container";
                }
                else if (exe is ForLoop)
                {
                    exeTypeName = "For Loop Container";
                }

                if (!(string.IsNullOrEmpty(exeTypeName)) && !(executables.Contains(exeTypeName)))
                {
                    executables.Add(exeTypeName);
                }
            }

            return executables;
        }

        public void Save()
        {
            Application a = new Application();
            a.SaveToXml(packageFile.FullName, ssisPackage, null);
        }

        public List<Variable> GetVariables()
        {
            List<Variable> variables = new List<Variable>();

            foreach (Variable v in ssisPackage.Variables)
            {
                if (v.Namespace != "System")
                {
                    variables.Add(v);
                }
            }
            return variables;
        }

        public List<Configuration> GetConfigurations()
        {
            List<Configuration> configurations = new List<Configuration>();

            foreach (Configuration c in ssisPackage.Configurations)
            {
                configurations.Add(c);
            }
            return configurations;
        }

        public List<ConnectionManager> GetConnectionManagers()
        {
            return GetConnectionManagers(false);
        }

        public List<ConnectionManager> GetConnectionManagers(bool oleDbOnly)
        {
            List<ConnectionManager> connectionManagers =
                new List<ConnectionManager>();

            foreach (ConnectionManager c in ssisPackage.Connections)
            {
                if (c.CreationName == "OLEDB" || !oleDbOnly)
                {
                    connectionManagers.Add(c);
                }
            }
            return connectionManagers;
        }

        public string GetValidateScript()
        {
            const string VALIDATE_TEMPLATE = "DTEXEC.EXE /FILE \"{0}\" /VALIDATE  /REPORTING EW";
            return string.Format(VALIDATE_TEMPLATE, packageFile.FullName);
        }

        public DTSExecResult Validate()
        {
            ValidationHelper vh = new ValidationHelper(this.SsisPackage);

            DTSExecResult result = vh.Validate();
            this.validationStatus = result.ToString();

            this.errorMessages = vh.ErrorMessages;
            this.infoMessages = vh.InfoMessages;
            this.warningMessages = vh.WarningMessages;

            return result;
        }

        public DTSExecResult CustomValidate()
        {
            // Set all "DelayValidation" properties to false
            foreach (Executable ex in this.ssisPackage.Executables)
            {
                if (ex is TaskHost)
                {
                    TaskHost th = ex as TaskHost;
                    th.DelayValidation = false;
                }
            }

            // perform the actual validation
            return this.Validate();
        }

        #region SetVersion Overloads

        public void SetVersion(int versionMajor, int versionMinor, 
            string versionComments, bool resetVersionBuild)
        {
            ssisPackage.VersionMinor = versionMinor;
            ssisPackage.VersionMajor = versionMajor;
            ssisPackage.VersionComments = versionComments;

            if (resetVersionBuild)
            {
                ssisPackage.VersionBuild = 0;
            }
        }

        public void SetVersion(int versionMajor, int versionMinor,
            string versionComments)
        {
            this.SetVersion(versionMajor, versionMinor, versionComments, false);
        }

        public void SetVersion(int versionMajor, int versionMinor)
        {
            this.SetVersion(versionMajor, versionMinor, string.Empty, false);
        }

        #endregion

        #region CreateVariable Overloads

        public void CreateVariable(string variableName, TypeCode dataType,
            object value)
        {
            CreateVariable(variableName, dataType, false,
                "User", value, false, string.Empty, string.Empty);
        }

        public void CreateVariable(string variableName, TypeCode dataType,
            bool readOnly, string variableNamespace, object value)
        {
            CreateVariable(variableName, dataType, readOnly, 
                variableNamespace, value, false,
                string.Empty, string.Empty);
        }

        public void CreateVariable(string variableName, TypeCode dataType, 
            bool readOnly, string variableNamespace, object value, 
            bool evaluateAsExpression, string expression,
            string description)
        {
            object variableValue;

            switch (variableNamespace)
            {
                case "System":
                    throw new ApplicationException("You cannot create new System variables");
                case "":
                    variableNamespace = "User";
                    break;
            }

            if (SsisHelper.IsCastValid(dataType, value))
            {
                variableValue = Convert.ChangeType(value, dataType);
            }
            else
            {
                throw new ApplicationException(string.Format(
                    "Data type mismatch: unable to convert supplied value '{0}' into requested data type '{1}'",
                    value, dataType));
            }

            if (VariableExists(variableName, variableNamespace))
            {
                DeleteVariable(variableName, variableNamespace);
            }

            Variable v = ssisPackage.Variables.Add(variableName, readOnly, variableNamespace,
                variableValue);

            v.Description = description;

            if (evaluateAsExpression)
            {
                v.EvaluateAsExpression = true;
                v.Expression = expression;
            }
        }

        #endregion

        #region VariableExists Overloads

        public bool VariableExists(string variableName, string variableNamespace)
        {
            return this.ssisPackage.Variables.Contains(
                SsisHelper.GetQualifiedVariableName(variableName, variableNamespace));
        }

        public bool VariableExists(string variableName)
        {
            if (variableName.IndexOf("::") < 0)
            {
                return VariableExists(variableName, "User");
            }
            else
            {
                return this.ssisPackage.Variables.Contains(variableName);
            }
        }

        #endregion

        #region AssignVariableValue Overloads

        public void AssignVariableValue(string variableName,
            object variableValue)
        {
            if (variableName.IndexOf("::") < 0)
            {
                // The variable name does not contain a namespace - assume User
                AssignVariableValue(variableName, variableValue, "User");
            }
            else
            {
                // The supplied variable name contains a namespace
                this.ssisPackage.Variables[variableName].Value = variableValue;
            }
        }

        public void AssignVariableValue(string variableName,
            object variableValue, string variableNamespace)
        {
            string qualifiedVariableName = variableNamespace + "::" + variableName;
            
            this.ssisPackage.Variables[qualifiedVariableName].Value = variableValue;
        }

        #endregion

        /// <summary>
        /// Apply the selected configuration to the supplied package
        /// </summary>
        /// <param name="ssisPackage"></param>
        /// <param name="configurationName"></param>
        /// <remarks>
        /// Note: The default configuration will also always be applied
        /// </remarks>
        public void ApplyConfigurationSubset(string fileName, 
            string configurationSubsetName)
        {
            XmlConfigHelper configHelper = new XmlConfigHelper(fileName);

            if (configurationSubsetName != DEFAULT_CONFIG_SUBSET_NAME)
            {
                // If we're not dealing with the default configuration here,
                // we need to first apply the default before applying the 
                // specied configuration subset
                configHelper.ConfigureFromXmlStore(DEFAULT_CONFIG_SUBSET_NAME, 
                    ssisPackage);
            }
            configHelper.ConfigureFromXmlStore(configurationSubsetName,
                    ssisPackage);
        }

        public Dictionary<string, string> GetConfiguredVariableValues(
            string configurationSubsetName, string xmlConfigFileName)
        {
            return new XmlConfigHelper(xmlConfigFileName).GetConfiguredVariableValues(
                ssisPackage, configurationSubsetName);
        }


        #region DeleteVariable Overloads

        public void DeleteVariable(Variable variable)
        {
            DeleteVariable(SsisHelper.GetQualifiedVariableName(variable));
        }

        public void DeleteVariable(string variableName)
        {
            if (ssisPackage.Variables.Contains(variableName))
            {
                ssisPackage.Variables.Remove(variableName);
            }
            else
            {
                throw new ApplicationException(
                    string.Format("The variable specified '{0}' does not exist.",
                        variableName));
            }
        }

        public void DeleteVariable(string variableName, 
            string variableNamespace)
        {
            switch (variableNamespace)
            {
                case "System":
                    throw new ApplicationException(
                        "You cannot delete System variables.");
                case "":
                    variableNamespace = "User";
                    break;
            }
            DeleteVariable(SsisHelper.GetQualifiedVariableName(variableName,
                variableNamespace));
        }

        #endregion

        public void DeleteConfiguration(string configurationName)
        {
            if (ssisPackage.Configurations.Contains(configurationName))
            {
                ssisPackage.Configurations.Remove(configurationName);
            }
        }

        public void DeleteConnectionManager(string connectionManagerName)
        {
            if (ssisPackage.Connections.Contains(connectionManagerName))
            {
                ssisPackage.Connections.Remove(connectionManagerName);
            }
        }

        public void UpdateConnectionManagerExpression(
            string connectionManagerName, string propertyName,
            string expression)
        {
            if (ssisPackage.Connections.Contains(connectionManagerName))
            {
                ConnectionManager conn = ssisPackage.Connections[connectionManagerName];
                conn.Properties[propertyName].SetExpression(conn, expression);
            }
        }

        public void ClearDataSurceBinding(string connectionManagerName)
        {
            if (ssisPackage.Connections.Contains(connectionManagerName))
            {
                ConnectionManager conn = ssisPackage.Connections[connectionManagerName];

            }
        }


    }
}
